import { Injectable } from '@nestjs/common';
import { InjectRepository } from '@nestjs/typeorm';
// 连接数据库 中的表
import { Repository, getRepository, Connection } from 'typeorm';
import { MoreThan, LessThan, Between } from 'typeorm';
import { test_jy, jy_data, summary } from 'src/entities/Wealth.entity';
import { WealthService_jyData } from './wealth_jyData.service';
import * as dayjs from 'dayjs';
import * as colors from 'colors';
/// <reference path="./wealth_types.ts" />

interface RepositoryData {
  id: number;
  jy_code: string;
  jy_name: string;
  jy_mon: string;
  jy_buy_date: string;
  jy_del_date: string;
}
interface Init<T> {
  code: number;
  data: any;
  msg: string;
}

/* 数据处理 */
@Injectable()
export class WealthService {
  constructor(
    // 注入实体
    @InjectRepository(test_jy)
    private readonly testJyRepository: Repository<test_jy>, //@InjectRepository( jy_data )
    // Repository<XXX> 连接表
    // testJyRepository: Repository<test_jy>,
    // private readonly jyDataRepository: Repository<jy_data>,
    private connection: Connection,
    // 连接另一种表，操作另一种表的数据
    private readonly WealthService_jyData: WealthService_jyData,
    @InjectRepository(summary)
    private readonly jyDataSummary: Repository<summary>,
  ) {}

  // wealth 数据测试
  async getInit(): Promise<Init<RepositoryData>> {
    const testJy = await this.testJyRepository.find();
    // const jyData = await this.jyDataRepository.find()
    // const jyData = await getRepository(jy_data)

    // 设置连接池
    // 单独连接 表格 操作数据
    // const testAAA = await this.connection
    //   .getRepository(jy_data)
    //   .createQueryBuilder('jy_data__test')
    //   .getRawMany()
    // console.log( 'testAAA !!!', testAAA )

    // const jyData = await this.WealthService_jyData.get_jyData()
    // const getWhere_jyData = await this.testJyRepository.query(
    //   "SELECT * FROM jy_data;"
    // )

    const startDate = dayjs().add(-1, 'day').format('YYYY-MM-DD 00:00:00');
    const endDate = dayjs().format('YYYY-MM-DD HH:mm:ss');
    const qbWhere = {
      NOEDATE: Between(startDate, endDate),
    };
    // 获取表中的全部字段
    const allSelects =
      this.connection.getRepository(jy_data).metadata.propertiesMap;
    const sum = await this.connection.getRepository(jy_data).find({
      select:
        allSelects && allSelects instanceof Object
          ? Object.values(allSelects)
          : ['NOEDATE', 'Zqmc'], // 需要查询的字段
      where: qbWhere,
    });

    // const jyData = await this.getSummeryData()
    // console.log('connection', this.connection,  )

    return {
      code: 0,
      data: [...sum], // [ ...jyData, ...getWhere_jyData], //...testJy,
      msg: 'wealth 请求成功 哈哈哈~~~！！！',
    };
  }

  // 根据条件筛选需要的数据
  async getWhere(): Promise<any> {
    // const getWhere = await this.testJyRepository.find({where: { jy_mon: 100 }})
    // 插入 sql 代码
    const getWhere = await this.testJyRepository.query(
      'SELECT * FROM test_jy WHERE  jy_mon >= 0 and jy_mon <= 5000;',
    );
    // const getWhere_jyData = await this.testJyRepository.query(
    //   "SELECT * FROM jy_data;"
    // )
    // const getWhere_jyData = await getRepository(jy_data)

    // console.log('getWhere... getWhere_jyData...', getWhere_jyData )

    return {
      code: 0,
      data: [...getWhere], // ...getWhere_jyData
      msg: 'where~~~',
    };
  }

  // 汇总数据
  async getSummeryData(time?: {
    sTime?: string;
    eTime?: string;
  }): Promise<any> {
    // const nowDate = new Date().toLocaleString().split(' ')[0]
    // const startDate = time.sTime || new Date(new Date(nowDate).getTime() - 86400000).toLocaleString().split(" ")[0].replace(/\//g, '-')+" 0:0:0"
    // const endDate = time.eTime || nowDate.replace(/\//g, '-') + " 23:59:59"
    const startDate =
      time?.sTime || dayjs().add(-2, 'day').format('YYYY-MM-DD 00:00:00');
    const endDate = time?.eTime || dayjs().format('YYYY-MM-DD HH:mm:ss');

    // console.log('时间---》', startDate.red, endDate.black, typeof endDate)

    // const sum = await this.testJyRepository.query(
    //   `SELECT * FROM summary WHERE date <= "2022-8-15 23:59:59" AND date >= '2022-8-15 0:0:0'`
    // )
    /*
      使用 createQueryBuilder 语法构建 SQL 查询，执行它们并获得自动转换的实体
    */
    const sum = await this.jyDataSummary
      .createQueryBuilder('summary')
      .where('date BETWEEN :startTime AND :endTime', {
        startTime: startDate,
        endTime: endDate,
      })
      // .andWhere('date BETWEEN :startTime AND :endTime', {
      //   startTime: startDate,
      //   endTime: endDate
      // })
      .getMany();

    return sum;
  }

  // 条件查询
  async postWealthCondition(params: WealthTypes.PWC) {
    const { startTime, endTime, codeName } = params;

    console.log('codeName', codeName);
    /**
     * 使用SQL语句查询 日期查询
     */
    const getWhere = await this.testJyRepository.query(
      `SELECT * FROM jy_data WHERE NOEDATE BETWEEN CAST('2023-04-13' AS DATE) AND CAST('2023-04-14' AS DATE);`,
    );

    /**
     *  创建queryBuilder SQL 连接方法
     */
    // let qb = await getRepository(jy_data).createQueryBuilder('jyData')
    // .where('NOEDATE BETWEEN :start AND :end', { // 创建日期查询
    //   start: '2023-04-13', // startTime,
    //   end: endTime
    // })
    // .andWhere('jyData.Zqmc = :codeName', { // 添加其他条件
    //   name: '星网宇达', // codeName
    // })
    // .getMany();

    /**
     * 使用 typeorm封装好的api方法
     */
    const qbWhere = {
      NOEDATE: Between(startTime, endTime),
    };
    if (codeName) qbWhere['Zqmc'] = codeName;
    const qbData = await this.connection.getRepository(jy_data).find({
      select: ['NOEDATE', 'Zqmc'], //需要的字段
      where: qbWhere,
      // where: { // 条件查询
      //   // start: MoreThan('2023-04-13'), // startTime, MoreThan 大于
      //   // end: LessThan(endTime) // LessThan 小于
      //   NOEDATE: Between('2023-04-10', endTime), // Between 在区间之内
      //   Zqmc: '', // codeName
      // }
    });

    return {
      // code: 0,
      data: qbData, // getWhere,
      msg: 'postWealthCondition~~~',
    };
  }
}
